<?php

/**
 * 検索用SQL作成クラス<br />
 *
 * @package apamanshop
 * @author jschoi
 * @copyright
 *
 */
class Mobileapi_Model_Search extends Apm_Model_Base
{

    /**
     * 駅候補取得
     *
     * パラメータの駅名からヴァルデータ取得
     *
     * @access public
     * @param array パラメータ
     * @return array 対象項目データ
     */
    public function getTsukinTakingTimeTargetStation($params)
    {
        $rtn = array();
        $paramekis = array();

        // 環境定数取得
        $configObject = Zend_Registry::get('config');
        $config = $configObject->apm->toArray();

        $condition = $params['tsukintargetekicd'];

        $parts = preg_split('/\;/', $condition, NULL, PREG_SPLIT_NO_EMPTY);

        for ($i = 0; $i < count($parts); $i++)
        {
            $getData = array();

            $vals = preg_split('/\:/', $parts[$i], NULL, NULL);
            $ekis = preg_split('/\./', $vals[0], NULL, PREG_SPLIT_NO_EMPTY);

            $client = new Zend_Http_Client("http://apaman.ekiworld.net/exp.cgi");
            $client->setMethod(Zend_Http_Client::POST);

            $client->setParameterPost(array(
                "val_htmb" => "ams_range",
                "val_fromcode" => $ekis[0],
                "val_range_start" => "0",
                "val_range_end" => $vals[1],
                "val_type" => "1",
                "val_transfer_count" => $vals[2]
            ));
            $response = $client->request();

            if ($response->isError())
            {
                $getData[0] = "0";
            }
            else
            {

                $getData = explode(",", $response->getBody());

                $ekilist = array_slice($getData, 3);

                for ($j = 0; $j < count($ekilist); $j++)
                {
                    if (count($paramekis) < 999)                     // オラクルの制限
                    array_push($paramekis, "'" . substr($ekilist[$j], 0, 5) . "'");
                }
            }
        }

        $sql .= "    SELECT DISTINCT";
        $sql .= "         M03.M03_TODOUFUKEN_CD,M03.M03_EKI_NAME";
        $sql .= "             ,(SELECT CAST(WM_CONCAT(M03.M03_TODOUFUKEN_CD || '-' || SUB.M03_EKI_CD) AS VARCHAR(1024)) FROM M03EKI SUB ";
        $sql .= "                    WHERE SUB.M03_TODOUFUKEN_CD = M03.M03_TODOUFUKEN_CD AND SUB.M03_EKI_NAME = M03.M03_EKI_NAME";
        $sql .= "             ) AS FULL_EKI_CD";
        $sql .= "             FROM";
        $sql .= "         M03EKI M03";
        $sql .= "             WHERE";
        $sql .= "                 M03_ROSENZU_EKI_CD in (";
        $sql .= implode(",", $paramekis);
        $sql .= ")";

        return $this->dbGetList($sql);
    }

    /**
     * 通勤通学時間検索用SQL取得
     *
     * @access public
     * @param array
     * @param array ※参照渡し
     * @return string
     */
    public function getTsukinTakingTimeEkiData($params, $valData)
    {
        $db_params = array();
        // 沿線取得SQL作成
        $sql = $this->getFromWhereForTsukintsugakujikansearch($params, $db_params);

        return $this->dbGetList($sql, $db_params);
    }

    /**
     * 通勤通学時間検索用SQL取得
     *
     * @access public
     * @param array
     * @param array ※参照渡し
     * @return string
     */
    public function getFromWhereForTsukintsugakujikansearch($params, &$db_params)
    {

        // データ格納
        $condition = $params['tsukintargetekicd'];

        $parts = preg_split('/\;/', $condition, NULL, PREG_SPLIT_NO_EMPTY);

        for ($i = 0; $i < count($parts); $i++)
        {
            $vals = preg_split('/\:/', $parts[$i], NULL, NULL);

            $ekis = preg_split('/\./', $vals[0], NULL, PREG_SPLIT_NO_EMPTY);

            $db_params['ekiCd' . $i] = $ekis[0];
            $db_params['fromTime' . $i] = 0;
            $db_params['toTime' . $i] = $vals[1];

            if ($vals[2] != '') $db_params['transferCount' . $i] = $vals[2];
        }

        $sql .= "    SELECT DISTINCT";
        $sql .= "         M03.M03_TODOUFUKEN_CD,M03.M03_EKI_NAME";
        $sql .= "              ,T94.T94_TARGET_EKI_CD";
        $sql .= "             ,(SELECT CAST(WM_CONCAT(M03.M03_TODOUFUKEN_CD || '-' || SUB.M03_EKI_CD) AS VARCHAR(1024)) FROM M03EKI SUB ";
        $sql .= "                    WHERE SUB.M03_TODOUFUKEN_CD = M03.M03_TODOUFUKEN_CD AND SUB.M03_EKI_NAME = M03.M03_EKI_NAME";
        $sql .= "             ) AS FULL_EKI_CD";
        $sql .= "             FROM";
        $sql .= "         T94COMMUTING_EKI_T03SUM T94, M03EKI M03";
        $sql .= "             WHERE";
        $sql .= "                 T94.T94_EKI_CD = M03.M03_EKI_CD AND (";
        for ($i = 0; $i < count($parts); $i++)
        {
            if ($i > 0) $sql .= " OR ";
            $sql .= "          (T94.T94_TARGET_EKI_CD = :ekiCd" . $i;
            $sql .= "         		AND T94.T94_TARGET_EKI_TIME BETWEEN :fromTime" . $i . " AND :toTime" . $i;

            if ($db_params['transferCount' . $i] != '') $sql .= "         AND T94.T94_TRANSFER_COUNT <= :transferCount" . $i;

            $sql .= ")";
        }

        $sql .= ")";
        // $sql .=" order by T94.T94_TARGET_EKI_TIME";

        return $sql;
    }

}
?>